package com.gljx.web.controller;

import com.gljx.constant.ActivitiKey;
import com.gljx.util.*;
import com.gljx.web.Entity.*;
import com.gljx.web.service.CustomLogService;
import com.gljx.web.service.DepartmentService;
import com.gljx.web.service.SkillEntryService;
import com.gljx.web.service.SkillService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.text.SimpleDateFormat;
import java.util.*;

@Controller
@RequestMapping("/")
public class SkillController {

	@Autowired
	private SkillService SkillService;
	
	@Autowired
	private DepartmentService departmentService;
	
	@Autowired
	private SkillEntryService SkillEntryService;
	
	@Autowired
	private StudentController studentController;
	
	@Autowired
	private CustomLogService customLogService;


	ActitiviUtil actitiviUtil = new ActitiviUtil();
	/**
	 * 查询科目一技能交接
	 * @param request
	 * @return
	 * @createTime 2017-11-24 10:16 am
	 * @author zyq.
	 * @throws Exception
	 */
	@RequestMapping(value="/selectSkill/look.action")
	public String selectSkill(HttpServletRequest request) throws Exception {
		Calendar ca = Calendar.getInstance();// 得到一个Calendar的实例
			     ca.setTime(new Date()); // 设置时间为当前时间
			     ca.add(Calendar.DATE, -1);// 月份减1
		Date resultDate = ca.getTime(); // 结果
		HttpSession session=request.getSession();
		User user=(User) session.getAttribute("user");                                   //获取已经登录用户数据
		Department department=departmentService.queryAdminByFid(user.getFcompanyid());   //获取登录用户组织ID
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String beginTime = sdf.format(resultDate);
		String endTime = sdf.format(new Date());	
		PageBean<Skill> pBean = getPageBean(request, department.getFid(), beginTime, endTime);
		//		rhc  信息总条数
		String messageAmount="共有"+pBean.getTr()+"条信息";
		for(Skill skill :pBean.getBeanList()){
		    skill.getFnumber();
        }
		request.setAttribute("pb", pBean);
		request.setAttribute("beginTime", beginTime);
		request.setAttribute("endTime", endTime);
		request.setAttribute("ActivitiKey",ActivitiKey.Driving_GL_ID.equals(GetUserSession.getUserSession(request)) ? ActivitiKey.DrivingSys_fileAndSkill_gl : ActivitiKey.DrivingSys_fileAndSkill_nc);
		request.setAttribute("skills", pBean.getBeanList());
		//		rhc  信息总条数
		request.setAttribute("messageAmount",messageAmount);
		return "backHome/skill/SkillList";
	}
	
	
	/**
	 * 查询副表
	 * @param fid
	 * @createTime 2017-11-29 9:46 am
	 * @author zyq.
	 * @return
	 */
	@ResponseBody
	@RequestMapping(value="/selSkillEntryByFid")
	public List<SkillEntry> selSkillEntryByFid(String fid) {
		List<SkillEntry> SkillEntries = SkillEntryService.selSkillEntryByFid(fid);
		return SkillEntries;
	}
	
	
	/**
	 * 新增科目一技能
	 * @param request
	 * @return
	 * @createTime 2017-11-29 11:46 am
	 * @author zyq.
	 * @throws Exception
	 */
	@RequestMapping(value="/addSkill")
	public String addSkill(HttpServletRequest request) throws Exception {
		HttpSession session=request.getSession();
		Calendar ca = Calendar.getInstance();// 得到一个Calendar的实例
		         ca.setTime(new Date()); // 设置时间为当前时间
		         ca.add(Calendar.DATE, -1);// 月份减1
	    Date resultDate = ca.getTime(); // 结果
	    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
	    String beginTime = sdf.format(resultDate);
	    String endTime = sdf.format(new Date());
		User user=(User) session.getAttribute("user");                                  		//获取已经登录用户数据
		Department department=departmentService.queryAdminByFid(user.getFcompanyid());  	   //获取登录用户组织ID
        Map map = studentController.getPageAsTrainCharge(request, "", "","0","", "", "",beginTime, endTime); //调用学员方法    //调用学员方法
		request.setAttribute("flag", 1);				//是否为新增记录	
		request.setAttribute("beginTime", beginTime);
        request.setAttribute("endTime", endTime);
		request.setAttribute("pb", map.get("pb"));
        request.setAttribute("students", map.get("sList"));
        request.setAttribute("department", department);
		return "backHome/skill/detailSkill";
	}
	
	
	/**
	 * 保存科目一技能交接
	 * @param request
	 * @createTime 2017-11-27 10:46 am
	 * @author zyq.
	 * @param Skill
	 * @param SkillEntry
	 * @return
	 */
	@RequestMapping(value="/saveSkill")
	public String saveSkill(HttpServletRequest request ,Skill Skill, SkillEntry SkillEntry,String cfsex1) {
		//获取操作人信息
		User user = (User) request.getSession().getAttribute("user");
		//利用UUID生成fid
		Skill.setFid(GetFid.UUIDIntoFid());
		//控制单元id
		Skill.setFcontrolunitid(user.getFcompanyid());
		//创建人id
		Skill.setFcreatorid(user.getFid());
		//公司id
		Skill.setCfficompanyid(user.getFcompanyid());
		//获取当前时间
		String createTime = TimeFormat.timeFormat(new Date());
		Skill.setFcreatetime(createTime);
        /*String fnumber = customLogService.selectFunmber("CT_DS_SUBJECTONESKILLSHANDOVER",user.getFcompanyid());*/
        Skill.setFnumber(GetFnumber.fnumbers("JN"));
		Skill.setCfbillstate((long) 3);
		Skill.setFhandlerid(user.getFid());
		if(cfsex1 != null){
			if(cfsex1.length() > 1){
		String [] cfsex = cfsex1.split(",");
		String [] cfclazz = (String[]) SkillEntry.getCfclazz();
		String [] studentid = SkillEntry.getCfstudentid().split(",");
		String [] cfidcardno = (String[]) SkillEntry.getCfidcardno();
		String [] cfstudentno = (String[]) SkillEntry.getCfstudentno();
		String [] cfcx = SkillEntry.getCfcx().split(",");
		String [] cfphone = (String[]) SkillEntry.getCfphone();
		String [] cfdesc = (String[]) SkillEntry.getCfdesc();
		for(int i=0;i<cfsex.length;i++) {
			SkillEntry.setCfclazz(cfclazz[i]);
			SkillEntry.setCfcx(cfcx[i]);
			SkillEntry.setCfdesc(cfdesc[i]);
			SkillEntry.setCfidcardno(cfidcardno[i]);
			SkillEntry.setCfstudentno(cfstudentno[i]);
			SkillEntry.setCfstudentid(studentid[i]);
			SkillEntry.setCfphone(cfphone[i]);
			Long sex ;
			if("null".equals(cfsex[i]) || "".equals(cfsex[i]))
				sex = null;
			else
				sex = new Long(cfsex[i]);
			SkillEntry.setCfsex(sex);
			SkillEntry.setFid(GetFid.UUIDIntoFid());
			SkillEntry.setFparentid(Skill.getFid());
			SkillEntryService.insertSkillEntry(SkillEntry);              //保存副表记录
			customLogService.addLog("科目一技能交接", "办理了学员："+SkillEntry.getCfstudentno()+SkillEntry.getCfstudentName()+"科目一档案交接,单据编号："+Skill.getFnumber(), studentid[i], user.getFid());
		}
		}
		else{
				Long sex = new Long(cfsex1);
				SkillEntry.setCfsex(sex);
			SkillEntry.setFid(GetFid.UUIDIntoFid());
			SkillEntry.setFparentid(Skill.getFid());
			SkillEntryService.insertSkillEntry(SkillEntry);              //保存副表记录
			customLogService.addLog("科目一技能交接", "办理了学员："+SkillEntry.getCfstudentno()+SkillEntry.getCfstudentName()+"科目一档案交接,单据编号："+Skill.getFnumber(), Skill.getCfstudentid(), user.getFid());
			}
		SkillService.insertSkill(Skill);

		}else{
				
			}
		request.setAttribute("type",2);
		return "forward:addSkill";
	}
	
	
	/**
	 * 根据FID查询技能交接
	 * @param request
	 * @param fid
	 * @return
	 * @createTime 2017-11-28 9:46 am
	 * @author zyq.
	 * @throws Exception 
	 */
	@RequestMapping(value="/selSkillById")
	public String selSkillById(HttpServletRequest request,String fid) throws Exception{
		Skill Skill = SkillService.selectSkillByFid(fid);
		if(Skill == null)
			return "/error/not_data";
		else {
				if (Skill.getCfbillstate() == 3) {
					Calendar ca = Calendar.getInstance();// 得到一个Calendar的实例
					ca.setTime(new Date()); // 设置时间为当前时间
					ca.add(Calendar.DATE, -1);// 月份减1
					Date resultDate = ca.getTime(); // 结果
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					String beginTime = sdf.format(resultDate);
					String endTime = sdf.format(new Date());
					List<SkillEntry> SkillEntries = SkillEntryService.selSkillEntryByFid(fid);
					Map map = studentController.getPageAsTrainCharge(request, "","","0", "", "","", beginTime, endTime); //调用学员方法 //调用学员方法
					request.setAttribute("beginTime", beginTime);
					request.setAttribute("endTime", endTime);
					request.setAttribute("pb", map.get("pb"));
					request.setAttribute("students", map.get("sList"));
					request.setAttribute("skill", Skill);
					request.setAttribute("skillEntries", SkillEntries);
					request.setAttribute("flag", 2);
				} else {
					List<SkillEntry> SkillEntries = SkillEntryService.selSkillEntryByFid(fid);
					request.setAttribute("skill", Skill);
					request.setAttribute("skillEntries", SkillEntries);
					request.setAttribute("flag", 2);
				}
			}
			//2.查询
			String result = actitiviUtil.getTaskStateByUser(
					ActivitiKey.Driving_GL_ID.equals(GetUserSession.getUserSession(request)) ? ActivitiKey.DrivingSys_fileAndSkill_gl : ActivitiKey.DrivingSys_fileAndSkill_nc, Skill.getFnumber()               //单据
			);
			String seeFlag = SkillService.selectAuditPersonByAdd(Skill.getFnumber(), result);
			request.setAttribute("seeFlag", seeFlag);
			request.setAttribute("ActivitiKey",ActivitiKey.Driving_GL_ID.equals(GetUserSession.getUserSession(request)) ? ActivitiKey.DrivingSys_fileAndSkill_gl : ActivitiKey.DrivingSys_fileAndSkill_nc);
			request.setAttribute("submitFlag", result);
			return "backHome/skill/detailSkill";
	}
	
	
	/**
	 * 审核技能一交接
	 * @param request
	 * @param fid
	 * @return
	 * @createTime 2017-11-28 8:46 am
	 * @author zyq.
	 * @throws Exception
	 */
	@RequestMapping(value="/checkSkillBillState")
	@ResponseBody
	public ServerResponse checkSkillBillState(HttpServletRequest request,String fid,String state,String agreed,String auditcomment,String fnumber,String submitFlag) throws Exception {
		/*User user=(User) request.getSession().getAttribute("user");
		Skill Skill = new Skill();
					Skill.setFauditorid(user.getFid());
					Skill.setCfbillstate((long) 5);
					Skill.setFid(fid);
					SkillService.updateSkill(Skill);
		List<SkillEntry> SkillEntries = SkillEntryService.selSkillEntryByFid(fid);
		Student student = new Student();
		for(SkillEntry SkillEntry:SkillEntries){
			student.setCfjnbjj((long) 1);
			student.setFid(SkillEntry.getCfstudentid());
			studentService.updateStudentSelective(student);
		}
		customLogService.addLog("技能一交接", user.getName() + TimeFormat.timeFormat(new Date()) + " 审核了技能一交接 ", Skill.getFid(), user.getFid());  //生成日志
		selectSkill(request);
		return "backHome/skill/SkillList";*/
		User user=(User) request.getSession().getAttribute("user");
		List<SkillEntry> skillEntries = SkillEntryService.selSkillEntryByFid(fid);
		Skill   skill = new Skill();
				skill.setFauditorid(user.getFid());
				skill.setCfbillstate((long) 5);
				skill.setFid(fid);
				skill.setFnumber(fnumber);
		for(SkillEntry skillEntry :skillEntries) {
			if ("3".equalsIgnoreCase(state))
				customLogService.addLog("科目一技能交接", "反审核了学员：" +skillEntry.getCfstudentno()+ skillEntry.getCfstudentName() + "科目一技能交接,单据编号："+skill.getFnumber(), skillEntry.getCfstudentid(), user.getFid());  //生成日志
			else
				customLogService.addLog("科目一技能交接", "审核了学员："+skillEntry.getCfstudentno()+ skillEntry.getCfstudentName() + "科目一技能交接,单据编号："+skill.getFnumber(), skillEntry.getCfstudentid(), user.getFid());  //生成日
		}
		return ServerResponse.createBySuccessMessage(SkillService.checkSkill(skillEntries,skill,state,agreed,auditcomment,submitFlag,request));
	}

	/**
	 * 提交
	 * @param fnumber
	 * @return
	 */
	@RequestMapping(value = "/submitSkill")
	@ResponseBody
	public ServerResponse submitSkill(String fnumber,String fid,String submitFlag,HttpServletRequest request){
		User user=(User) request.getSession().getAttribute("user");
		Skill Skill = new Skill();
			  Skill.setFid(fid);
			  Skill.setFnumber(fnumber);
			  Skill.setCfbillstate((long) 4);
		Skill Skill2 = SkillService.selectSkillByFid(fid);
		List<SkillEntry> skillEntries = SkillEntryService.selSkillEntryByFid(Skill2.getFid());
		SkillService.submitFlow(Skill,submitFlag,request);
		SkillService.updateSkill(Skill);
		if (skillEntries != null ) {
			for(SkillEntry skillEntry :skillEntries) {
				customLogService.addLog("科目一技能交接", "提交了学员："+skillEntry.getCfstudentno()+skillEntry.getCfstudentName()+"科目一技能交接,单据编号："+Skill2.getFnumber(), skillEntry.getCfstudentid(), user.getFid());
			}
		}
		return ServerResponse.createBySuccessMessage("提交成功");
	}
	
	/**
	 * 更新技能交接表
	 * @param request
	 * @param Skill
	 * @param SkillEntry
	 * @return
	 * @createTime 2017-11-28 9:46 am
	 * @author zyq.
	 * @throws Exception
	 */
	@RequestMapping(value="/updateSkill")
	public String updateSkill(HttpServletRequest request,Skill Skill, SkillEntry SkillEntry,String cfsex1) throws Exception {
		User user=(User) request.getSession().getAttribute("user");
		String [] fids = Skill.getFid().split(",");              //批量fid，用来删除副表
		String updateTime=TimeFormat.timeFormat(new Date());
		Skill.setFlastupdatetime(updateTime);
		Skill.setFlastupdateuserid(user.getFid());
		SkillService.updateSkill(Skill);
		SkillEntryService.delSkillEntryByPid(fids);
		if(cfsex1.length() > 1){
		String [] cfsex = cfsex1.split(",");
		String [] cfclazz = (String[]) SkillEntry.getCfclazz();
		String [] studentid = SkillEntry.getCfstudentid().split(",");
		String [] cfidcardno = (String[]) SkillEntry.getCfidcardno();
		String [] cfstudentno = (String[]) SkillEntry.getCfstudentno();
		String [] cfcx = SkillEntry.getCfcx().split(",");
		String [] cfphone = (String[]) SkillEntry.getCfphone();
		String [] cfdesc = (String[]) SkillEntry.getCfdesc();
		for(int i=0;i<cfsex.length;i++) {
			SkillEntry.setCfclazz(cfclazz[i]);
			SkillEntry.setCfcx(cfcx[i]);
			SkillEntry.setCfdesc(cfdesc[i]);
			SkillEntry.setCfidcardno(cfidcardno[i]);
			SkillEntry.setCfstudentno(cfstudentno[i]);
			SkillEntry.setCfstudentid(studentid[i]);
			SkillEntry.setCfphone(cfphone[i]);
			Long sex ;
			if("".equals(cfsex[i]) || "null".equals(cfsex[i]))
				 sex = null;
			else
				 sex = new Long(cfsex[i]);
			SkillEntry.setCfsex(sex);
			SkillEntry.setFid(GetFid.UUIDIntoFid());
			SkillEntry.setFparentid(Skill.getFid());
			SkillEntryService.insertSkillEntry(SkillEntry);              //保存副表记录
		}
		}else{
				Long sex = new Long(cfsex1);
				SkillEntry.setCfsex(sex);
				SkillEntry.setFid(GetFid.UUIDIntoFid());
				SkillEntry.setFparentid(Skill.getFid());
				SkillEntryService.insertSkillEntry(SkillEntry);              //保存副表记录
		}	
		selectSkill(request);
		request.setAttribute("type",2);
		return "backHome/skill/SkillList";
	}
	
	
	
	
	/**
	 * 批量删除记录
	 * @param request
	 * @param fid
	 * @createTime 2017-11-27 11:46 am
	 * @author zyq.
	 * @return
	 * @throws Exception 
	 */
	@RequestMapping(value="/delSkillById")
	public String  delSkillById(HttpServletRequest request ,String fid) throws Exception {
		String [] fids = fid.split(",");
		SkillService.delSkill(fids);
		SkillEntryService.delSkillEntryByPid(fids);
		selectSkill(request);
		return "backHome/skill/SkillList";
	}
	
	
	/**
	 * 根据条件查询科目一技能
	 * @param request
	 * @param Skill
	 * @param SkillEntry
	 * @createTime 2017-11-27 2:16 pm
	 * @author zyq.
	 * @return
	 * @throws Exception 
	 */
	@RequestMapping(value="/selSkillByCon")
	public String selSkillByCon(HttpServletRequest request ,Skill Skill ,SkillEntry SkillEntry,String beginTime,String endTime) throws Exception {
		HttpSession session=request.getSession();
		User user=(User) session.getAttribute("user");                                   //获取已经登录用户数据
		Department department=departmentService.queryAdminByFid(user.getFcompanyid());   //获取登录用户组织ID
		PageBean<Skill> pBean = getPageBean1(request, department.getFid(), beginTime, endTime, Skill, SkillEntry,false);
		//		rhc  信息总条数
		String messageAmount="共有"+pBean.getTr()+"条信息";
		//		rhc  信息总条数
		request.setAttribute("messageAmount",messageAmount);
		request.setAttribute("pb", pBean);
		request.setAttribute("beginTime", beginTime);
		request.setAttribute("endTime", endTime);
		request.setAttribute("skills", pBean.getBeanList());
		request.setAttribute("skill1", Skill);
		request.setAttribute("skillEntry1", SkillEntry);
		request.setAttribute("ActivitiKey",ActivitiKey.Driving_GL_ID.equals(GetUserSession.getUserSession(request)) ? ActivitiKey.DrivingSys_fileAndSkill_gl : ActivitiKey.DrivingSys_fileAndSkill_nc);
		if(("".equals(Skill.getFnumber())|| null == Skill.getFnumber())&& ("".equals(SkillEntry.getCfstudentName()) || null == SkillEntry.getCfstudentName()) && ("".equals(SkillEntry.getCfidcardno()) || null == SkillEntry.getCfidcardno())){
			return "backHome/skill/SkillList";
		}else{
		return "backHome/skill/SkillList1";
		}
	}

	@RequestMapping(value = "/outSkillExcel")
	public void outSkillExcel(HttpServletResponse response, HttpServletRequest request,
									SkillEntry skillEntry, Skill skill,String beginTime, String endTime) throws Exception {
		//获取操作人信息
		User user = (User) request.getSession().getAttribute("user");
		List<Object> data = new ArrayList<>();
		//查询数据
		PageBean<Skill> pBean =  getPageBean1(request,user.getFcompanyid(),beginTime,endTime,skill,skillEntry,true);
		for (Skill skill1 : pBean.getBeanList()) {
			data.add(skill1.getFnumber());//单据编号
			data.add(skill1.getFbizdate());//业务日期
			data.add(skill1.getFdescription());
			data.add(skill1.getFauditorName());
			data.add(skill1.getFcreateName());//制单人名称
			data.add(skill1.getCfstudentName());//学员
			data.add(skill1.getCfstudentno());//学员编码
			data.add(skill1.getCfidcardno());//身份证号
			data.add(skill1.getCfdesc());
			if (skill1.getCfbillstate() == 3) {
				data.add("保存");//单据状态
			} else if (skill1.getCfbillstate() == 4) {
				data.add("提交");//单据状态
			} else if (skill1.getCfbillstate() == 5) {
				data.add("审核");//单据状态
			} else {
				data.add("");//单据状态
			}
			if (skill1.getCfsex() == null) {
				data.add("");//性别
			} else if (skill1.getCfsex() == 2) {
				data.add("女");//性别
			} else {
				data.add("男");//性别
			}
			data.add(skill1.getCfcx());
			data.add(skill1.getCfphone());
			data.add(skill1.getCfclazz());

		}
		//头部数据
		String head[] = {"单据编码", "业务日期", "备注","审核人名称", "制单人名称","学员","学员编码", "身份证号码","备注","单据状态", "性别",
				"车型", "手机", "班级"};
		//长度
		Integer length[] = {7500, 3000, 6000, 3000, 3000, 3000, 6200, 6200, 6000, 3000,
                3000, 2600, 4000, 6000};
		try {
			//导出Excel
			CommonExport.importExcel(response, "科目一技能交接" + TimeFormat.getNowTime2() + ".xls", "科目一技能交接", data, head, length);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}



	@SuppressWarnings("unchecked")
	/**
	 * 返回PageBean实体类  包括记录总数，页码，每页记录集等  dmz 20160921
	 * @return 返回PageBean实体类 
	 * @throws Exception
	 */
	public PageBean<Skill> getPageBean(HttpServletRequest request,String fid,String beginTime,String endTime) throws Exception
	{

		int offset;//从第几条数据开始加载
		if(request.getParameter("pager.offset")==null){  
			offset=0;  
		}else{  
			offset=Integer.parseInt(request.getParameter("pager.offset"));  
		}	
		String psql="select  T.FID,to_char(t.FCREATETIME,'YYYY-MM-DD HH24:MI:SS') fcreatetime,"
				   +"to_char(t.FLASTUPDATETIME,'YYYY-MM-DD HH24:MI:SS') flastupdatetime,"
				   + "to_char(t.FBIZDATE,'yyyy-MM-DD')  fbizdate,"
				   + "T.FCREATORID, T.FCONTROLUNITID,T.FLASTUPDATEUSERID, T.FNUMBER, T.FHANDLERID, T.FDESCRIPTION, T.FHASEFFECTED, T.FAUDITORID, T.FSOURCEBILLID,"
				   + "T.FSOURCEFUNCTION, T.FFIVOUCHERED, T.CFFICOMPANYID,T.CFBILLSTATE, "
				   + "S.NAME AS FCREATENAME,S1.NAME AS FLASTUPDATENAME,S2.NAME AS FHANDLERNAME,S3.NAME AS FAUDITORNAME"
			   		+ "  FROM CT_DS_SUBJECTONESKILLSHANDOVER T "
			   		+ " LEFT JOIN T_E_USER S ON T.FCREATORID=S.FID LEFT JOIN T_E_USER S1 ON T.FLASTUPDATEUSERID=S1.FID "
			   		+ " LEFT JOIN T_E_USER S2 ON T.FHANDLERID=S2.FID LEFT JOIN T_E_USER S3 ON T.FAUDITORID=S3.FID "
			   		+ " WHERE t.CFFICOMPANYID= '"+fid+"' and to_char(t.FBIZDATE,'yyyy-MM-dd') BETWEEN '"+beginTime+"' AND '"+endTime+"'";
				psql +="order by FBIZDATE desc";
		int psize=10; //每页显示数
		int pindex=offset/psize;//+1  页码从0开始	
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap= SkillService.selectPage(pindex, psql, psize);
		PageBean<Skill> pb=new PageBean<>();
		pb.setPc(pindex); //页码
		Map<String, Object> paramMapCount=getPageBeanCount(psql); //取记录总数
		pb.setTr((int)paramMapCount.get("Pcount")); //记录总数
		pb.setPs(psize);
		pb.setBeanList((List<Skill>)paramMap.get("v_cur")); //每页数据集
		return pb;

	}

	
	
	@SuppressWarnings("unchecked")
	/**
	 * 返回PageBean实体类  包括记录总数，页码，每页记录集等  dmz 20160921
	 * @return 返回PageBean实体类 
	 * @throws Exception
	 */
	public PageBean<Skill> getPageBean1(HttpServletRequest request,String fid,String beginTime,String endTime,Skill Skill,SkillEntry SkillEntry,boolean isExcel) throws Exception
	{

		int offset;//从第几条数据开始加载
		if(request.getParameter("pager.offset")==null){  
			offset=0;  
		}else{  
			offset=Integer.parseInt(request.getParameter("pager.offset"));  
		}
		String psql = "";
		if(("".equals(Skill.getFnumber())|| null == Skill.getFnumber())&& ("".equals(SkillEntry.getCfstudentName()) || null == SkillEntry.getCfstudentName()) && ("".equals(SkillEntry.getCfidcardno()) || null == SkillEntry.getCfidcardno())){
            if(isExcel){
                psql = "select  J.CFSTUDENTNO, j.CFIDCARDNO, j.CFDESC, j.CFPHONE, j.CFSEX, " +
                        "j.CFCX, j.CFCLAZZ，u.fname_l2 as cfstudentName, T.FID,to_char(t.FCREATETIME,'YYYY-MM-DD HH24:MI:SS') fcreatetime,"
                        + "to_char(t.FLASTUPDATETIME,'YYYY-MM-DD HH24:MI:SS') flastupdatetime,"
                        + "to_char(t.FBIZDATE,'yyyy-MM-DD')  fbizdate,"
                        + "T.FCREATORID, T.FCONTROLUNITID,T.FLASTUPDATEUSERID, T.FNUMBER, T.FHANDLERID, T.FDESCRIPTION, T.FHASEFFECTED, T.FAUDITORID, T.FSOURCEBILLID,"
                        + "T.FSOURCEFUNCTION, T.FFIVOUCHERED, T.CFFICOMPANYID,T.CFBILLSTATE, "
                        + "S.NAME AS FCREATENAME,S1.NAME AS FLASTUPDATENAME,S2.NAME AS FHANDLERNAME,S3.NAME AS FAUDITORNAME"
                        + "  FROM CT_DS_SUBJECTONESKILLSHANDOVER T "
                        + " LEFT JOIN T_E_USER S ON T.FCREATORID=S.FID LEFT JOIN T_E_USER S1 ON T.FLASTUPDATEUSERID=S1.FID "
                        + " LEFT JOIN T_E_USER S2 ON T.FHANDLERID=S2.FID LEFT JOIN T_E_USER S3 ON T.FAUDITORID=S3.FID "
                        + " LEFT JOIN CT_DS_SUBJECTOSHE J ON T.FID = J.FPARENTID LEFT JOIN CT_DS_STUDENT U ON U.FID = J.CFSTUDENTID"
                        + " WHERE t.CFFICOMPANYID= '" + fid + "'";
                if (null == Skill.getFnumber() || Skill.getFnumber() == "") {

                } else {
                    psql += "AND T.FNUMBER LIKE '%" + Skill.getFnumber() + "%'";
                }
                if (null == SkillEntry.getCfidcardno() || SkillEntry.getCfidcardno() == "") {

                } else {
                    psql += "AND J.CFIDCARDNO LIKE '%" + SkillEntry.getCfidcardno() + "%'";
                }
                if (null == (SkillEntry.getCfstudentName()) || SkillEntry.getCfstudentName() == "") {

                } else {
                    psql += "AND U.FNAME_L2 LIKE '%" + SkillEntry.getCfstudentName() + "%'";
                }
                if (beginTime.equals(null) || beginTime.equals("") || endTime.equals(null) || endTime.equals("")) {
                } else {
                    psql += "and to_char(t.FBIZDATE,'yyyy-MM-dd') BETWEEN '" + beginTime + "' AND '" + endTime + "'";
                }
                psql += " order by FBIZDATE desc";
            } else{
                psql = "select  T.FID,to_char(t.FCREATETIME,'YYYY-MM-DD HH24:MI:SS') fcreatetime,"
                        + "to_char(t.FLASTUPDATETIME,'YYYY-MM-DD HH24:MI:SS') flastupdatetime,"
                        + "to_char(t.FBIZDATE,'yyyy-MM-DD')  fbizdate,"
                        + "T.FCREATORID, T.FCONTROLUNITID,T.FLASTUPDATEUSERID, T.FNUMBER, T.FHANDLERID, T.FDESCRIPTION, T.FHASEFFECTED, T.FAUDITORID, T.FSOURCEBILLID,"
                        + "T.FSOURCEFUNCTION, T.FFIVOUCHERED, T.CFFICOMPANYID,T.CFBILLSTATE, "
                        + "S.NAME AS FCREATENAME,S1.NAME AS FLASTUPDATENAME,S2.NAME AS FHANDLERNAME,S3.NAME AS FAUDITORNAME"
                        + "  FROM CT_DS_SUBJECTONESKILLSHANDOVER T "
                        + " LEFT JOIN T_E_USER S ON T.FCREATORID=S.FID LEFT JOIN T_E_USER S1 ON T.FLASTUPDATEUSERID=S1.FID "
                        + " LEFT JOIN T_E_USER S2 ON T.FHANDLERID=S2.FID LEFT JOIN T_E_USER S3 ON T.FAUDITORID=S3.FID "
                        + " WHERE t.CFFICOMPANYID= '" + fid +"'";
				if (beginTime.equals(null) || beginTime.equals("") || endTime.equals(null) || endTime.equals("")) {
				} else {
					psql += " and to_char(t.FBIZDATE,'yyyy-MM-dd') BETWEEN '" + beginTime + "' AND '" + endTime + "'";
				}
                psql += " order by FBIZDATE desc";
            }
        } else{
            psql = "select  J.CFSTUDENTNO, j.CFIDCARDNO, j.CFDESC, j.CFPHONE, j.CFSEX, " +
                    "j.CFCX, j.CFCLAZZ，u.fname_l2 as cfstudentName, T.FID,to_char(t.FCREATETIME,'YYYY-MM-DD HH24:MI:SS') fcreatetime,"
                    + "to_char(t.FLASTUPDATETIME,'YYYY-MM-DD HH24:MI:SS') flastupdatetime,"
                    + "to_char(t.FBIZDATE,'yyyy-MM-DD')  fbizdate,"
                    + "T.FCREATORID, T.FCONTROLUNITID,T.FLASTUPDATEUSERID, T.FNUMBER, T.FHANDLERID, T.FDESCRIPTION, T.FHASEFFECTED, T.FAUDITORID, T.FSOURCEBILLID,"
                    + "T.FSOURCEFUNCTION, T.FFIVOUCHERED, T.CFFICOMPANYID,T.CFBILLSTATE, "
                    + "S.NAME AS FCREATENAME,S1.NAME AS FLASTUPDATENAME,S2.NAME AS FHANDLERNAME,S3.NAME AS FAUDITORNAME"
                    + "  FROM CT_DS_SUBJECTONESKILLSHANDOVER T "
                    + " LEFT JOIN T_E_USER S ON T.FCREATORID=S.FID LEFT JOIN T_E_USER S1 ON T.FLASTUPDATEUSERID=S1.FID "
                    + " LEFT JOIN T_E_USER S2 ON T.FHANDLERID=S2.FID LEFT JOIN T_E_USER S3 ON T.FAUDITORID=S3.FID "
                    + " LEFT JOIN CT_DS_SUBJECTOSHE J ON T.FID = J.FPARENTID LEFT JOIN CT_DS_STUDENT U ON U.FID = J.CFSTUDENTID"
                    + " WHERE t.CFFICOMPANYID= '" + fid + "'";
            if (null == Skill.getFnumber() || Skill.getFnumber() == "") {

            } else {
                psql += "AND T.FNUMBER LIKE '%" + Skill.getFnumber() + "%'";
            }
            if (null == SkillEntry.getCfidcardno() || SkillEntry.getCfidcardno() == "") {

            } else {
                psql += "AND J.CFIDCARDNO LIKE '%" + SkillEntry.getCfidcardno() + "%'";
            }
            if (null == (SkillEntry.getCfstudentName()) || SkillEntry.getCfstudentName() == "") {

            } else {
                psql += "AND U.FNAME_L2 LIKE '%" + SkillEntry.getCfstudentName() + "%'";
            }
            if (beginTime.equals(null) || beginTime.equals("") || endTime.equals(null) || endTime.equals("")) {
            } else {
                psql += "and to_char(t.FBIZDATE,'yyyy-MM-dd') BETWEEN '" + beginTime + "' AND '" + endTime + "'";
            }
            psql +=  " order by FBIZDATE desc";
        }
        Map<String, Object> paramMapCount=getPageBeanCount(psql); //取记录总数
        int psize = 0;
        if(isExcel)
			if((int)paramMapCount.get("Pcount") == 0)
				psize = 1;
			else
				psize = (int)paramMapCount.get("Pcount");
        else
            psize=60; //每页显示数
		int pindex=offset/psize;//+1  页码从0开始
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap= SkillService.selectPage(pindex, psql, psize);
		PageBean<Skill> pb=new PageBean<>();
		pb.setPc(pindex); //页码
		pb.setTr((int)paramMapCount.get("Pcount")); //记录总数
		pb.setPs(psize);
		pb.setBeanList((List<Skill>)paramMap.get("v_cur")); //每页数据集
		return pb;

	}


	/**
	 * 取记录总数 dmz 20160921
	 * @param psql  SQL语句
	 * @return Map
	 * @throws Exception
	 */
	public Map<String, Object> getPageBeanCount(String psql) throws Exception
	{

		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap=SkillService.selectPageCount(psql);
		return paramMap;		
	}


	
}
